package com.android.projectsquirrel;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapterUpdated
{
    public static final String KEY_PROFILENAME = "profileName";
    public static final String KEY_DAY = "Day";
    public static final String KEY_MONTH = "Month";
    public static final String KEY_YEAR = "Year";
    public static final String KEY_HOUR = "Hour";
    public static final String KEY_MINUTE = "Minute";
    public static final String KEY_AMPM = "AMPM"; 
    public static final String KEY_ZIP = "ZIP";
    public static final String KEY_LATITUDE = "LATITUDE";
    public static final String KEY_LONGITUDE = "LONGITUDE";    
    public static final String KEY_EMAIL = "Email"; 
    public static final String KEY_GRAYSQUIRRELS = "NUM_GRAY_SQUIRRELS"; 
    public static final String KEY_FOXSQUIRRELS = "NUM_FOX_SQUIRRELS"; 
    public static final String KEY_SETTING = "SETTING";   
    public static final String KEY_NUTBEARING = "NUT_TREES"; 
    public static final String KEY_SEEDBEARING = "SEED_TREES"; 
    public static final String KEY_FRUITBEARING = "FRUIT_TREES"; 
    public static final String KEY_TINYSEEDED = "TINY_TREES"; 
    public static final String KEY_CONIFEROUS = "CON_TREES"; 
    public static final String KEY_OTHERTREES = "OTHER_TREES"; 
    public static final String KEY_BIRDFEEDER = "FEED_BIRD_FEEDER";
    public static final String KEY_HANDOUTS = "FEED_HANDOUTS";
    public static final String KEY_GARBAGE = "FEED_GARBAGE";
    public static final String KEY_TREES = "FEED_TREES";
    public static final String KEY_FEED_OTHER  = "FEED_OTHER";
    public static final String KEY_FEED_OTHERDETAIL = "FEED_OTHER_DETAILS";
    public static final String KEY_SQUIRRELCHANGE = "NUM_SQUIRREL_CHANGE";
    public static final String KEY_DOGS = "SITE_DOGS";
    public static final String KEY_CATS= "SITE_CATS";
    public static final String KEY_COYOTES = "SITE_COYOTES";
    public static final String KEY_HAWKS = "SITE_HAWKS";
    public static final String KEY_GRAIN = "SITE_GRAIN";
    public static final String KEY_NEWSITE = "THIS_NEW_SITE";
    public static final String KEY_DIFFERENTSITE = "USED_DIFFERENT_SITE";
    public static final String KEY_COMMENTS = "COMMENTS";
    public static final String TAG = "DBAdapter";
    private static final String DATABASE_NAME = "ProjectSquirrelDatabase";
    private static final String DATABASE_TABLE = "ProjectSquirrelLookup";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "create table "+ DATABASE_TABLE+" (profileName text primary key,"
        + "Day integer ,Month integer,Year integer,Hour integer, Minute integer," 
        +"AMPM text,ZIP text,LATITUDE text,LONGITUDE text,"
        + "Email text,NUM_GRAY_SQUIRRELS integer, NUM_FOX_SQUIRRELS integer,SETTING text,NUT_TREES text,SEED_TREES text,FRUIT_TREES text,TINY_TREES text, CON_TREES text,"
		+"OTHER_TREES text,FEED_BIRD_FEEDER text,FEED_HANDOUTS text,FEED_GARBAGE text,FEED_TREES text,FEED_OTHER text,FEED_OTHER_DETAILS text,NUM_SQUIRREL_CHANGE text," 
		+"SITE_DOGS text,SITE_CATS text,SITE_COYOTES text,SITE_HAWKS text,SITE_GRAIN text,THIS_NEW_SITE text,"
		+"USED_DIFFERENT_SITE text,COMMENTS text);";
        
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapterUpdated(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
            onCreate(db);
        }
    }    
    
    //---opens the database---
    public DBAdapterUpdated open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
    
    //---insert a title into the database---
    public long insertSurveyPart1(String profilename,int submittedbefore,String zipcode,String latitude,String longitude, int setting,int nutbearing,
    		int seedbearing,int fruitbearing,int tinyseeded,int coniferous,String otherTrees)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_PROFILENAME, profilename);
        initialValues.put(KEY_NEWSITE, submittedbefore);
        initialValues.put(KEY_ZIP, zipcode);
        initialValues.put(KEY_LATITUDE, latitude);
        initialValues.put(KEY_LONGITUDE, longitude);
        initialValues.put(KEY_SETTING, setting);
        initialValues.put(KEY_NUTBEARING, nutbearing);
        initialValues.put(KEY_SEEDBEARING, seedbearing);
        initialValues.put(KEY_FRUITBEARING, fruitbearing);
        initialValues.put(KEY_TINYSEEDED, tinyseeded);
        initialValues.put(KEY_CONIFEROUS, coniferous);
        initialValues.put(KEY_OTHERTREES, otherTrees);
        long value =  db.insert(DATABASE_TABLE, null, initialValues);
//        db.close();
        return value;
    }

    //---deletes a particular title---
//    public boolean deleteTitle(long rowId) 
//    {
//        return db.delete(DATABASE_TABLE, KEY_ROWID + 
//        		"=" + rowId, null) > 0;
//    }

    //---retrieves all the titles---
    public Cursor getAllTitles() 
    {   
       Cursor c =  db.query(DATABASE_TABLE, new String[] {
    		   KEY_PROFILENAME, 
	       		KEY_DAY,
	       		KEY_MONTH,
	       		KEY_YEAR,
               KEY_HOUR,
               KEY_MINUTE, 
               KEY_AMPM,
               KEY_ZIP,
               KEY_LATITUDE,
               KEY_LONGITUDE,
               KEY_EMAIL,
               KEY_GRAYSQUIRRELS,
               KEY_FOXSQUIRRELS,
               KEY_SETTING,
               KEY_NUTBEARING,
               KEY_SEEDBEARING,
               KEY_FRUITBEARING,
               KEY_TINYSEEDED,
               KEY_CONIFEROUS,
               KEY_OTHERTREES,
               KEY_BIRDFEEDER,
               KEY_HANDOUTS,
               KEY_GARBAGE,
               KEY_TREES,
               KEY_FEED_OTHER,
               KEY_FEED_OTHERDETAIL,
               KEY_SQUIRRELCHANGE,
               KEY_DOGS,
               KEY_CATS,
               KEY_COYOTES,
               KEY_HAWKS,
               KEY_GRAIN,
               KEY_NEWSITE,
               KEY_DIFFERENTSITE,
               KEY_COMMENTS
       		},
                null, 
                null, 
                null, 
                null, 
                null);
       
       return c;
    }
//
//    //---retrieves a particular title---
    public Cursor getTitle(String profileName) throws SQLException 
    {
        Cursor mCursor =
                db.query(true,DATABASE_TABLE, new String[] {
                		KEY_PROFILENAME, 
                		KEY_DAY,
                		KEY_MONTH,
                		KEY_YEAR,
                        KEY_HOUR,
                        KEY_MINUTE, 
                        KEY_AMPM,
                        KEY_ZIP,
                        KEY_LATITUDE,
                        KEY_LONGITUDE,
                        KEY_EMAIL,
                        KEY_GRAYSQUIRRELS,
                        KEY_FOXSQUIRRELS,
                        KEY_SETTING,
                        KEY_NUTBEARING,
                        KEY_SEEDBEARING,
                        KEY_FRUITBEARING,
                        KEY_TINYSEEDED,
                        KEY_CONIFEROUS,
                        KEY_OTHERTREES,
                        KEY_BIRDFEEDER,
                        KEY_HANDOUTS,
                        KEY_GARBAGE,
                        KEY_TREES,
                        KEY_FEED_OTHER,
                        KEY_FEED_OTHERDETAIL,
                        KEY_SQUIRRELCHANGE,
                        KEY_DOGS,
                        KEY_CATS,
                        KEY_COYOTES,
                        KEY_HAWKS,
                        KEY_GRAIN,
                        KEY_NEWSITE,
                        KEY_DIFFERENTSITE,
                        KEY_COMMENTS
                		}, 
                		KEY_PROFILENAME+"=?",new String[]{profileName},
                		null,
                		null, 
                		null, 
                		null 
                		);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

//    //---updates a title---
    public boolean insertSurveyPart2(String profileName, int dogs, int cats,int coyotes,int hawks,int grain,int birdfeeder,
    		int handouts,int garbage,int treefeed,int othertrees,String otherfoodSources) 
     
    {
        ContentValues args = new ContentValues();
        args.put(KEY_DOGS, dogs);
        args.put(KEY_CATS, cats);
        args.put(KEY_COYOTES, coyotes);
        args.put(KEY_HAWKS, hawks);
        args.put(KEY_GRAIN, grain);
        args.put(KEY_BIRDFEEDER, birdfeeder);
        args.put(KEY_HANDOUTS, handouts);
        args.put(KEY_GARBAGE, garbage);
        args.put(KEY_TREES, treefeed);
        args.put(KEY_OTHERTREES, othertrees);
        args.put(KEY_FEED_OTHERDETAIL, otherfoodSources);
        return db.update(DATABASE_TABLE, args, 
        		KEY_PROFILENAME+"=?",new String[]{profileName})>0;
    }
    
    public boolean updateSurveyPart1(String profilename,int submittedbefore,String zipcode,String latitude,String longitude, int setting,int nutbearing,
    		int seedbearing,int fruitbearing,int tinyseeded,int coniferous,String otherTrees) 
     
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NEWSITE, submittedbefore);
        args.put(KEY_ZIP, zipcode);
        args.put(KEY_LATITUDE, latitude);
        args.put(KEY_LONGITUDE, longitude);
        args.put(KEY_SETTING, setting);
        args.put(KEY_NUTBEARING, nutbearing);
        args.put(KEY_SEEDBEARING, seedbearing);
        args.put(KEY_FRUITBEARING, fruitbearing);
        args.put(KEY_TINYSEEDED, tinyseeded);
        args.put(KEY_CONIFEROUS, coniferous);
        args.put(KEY_OTHERTREES, otherTrees);
        return db.update(DATABASE_TABLE, args, 
        		KEY_PROFILENAME+"=?",new String[]{profilename})>0;
    }
    
    public boolean insertSurveyPart3(String profileName, int numGray, int numFox,int day,int month,int year,int hour,
    		int minute,String ampm,String email,int differentSite,int squirrelchange,String comments) 
     
    {
        ContentValues args = new ContentValues();
        args.put(KEY_GRAYSQUIRRELS, numGray);
        args.put(KEY_FOXSQUIRRELS, numFox);
        args.put(KEY_DAY, day);
        args.put(KEY_MONTH, month);
        args.put(KEY_YEAR, year);
        args.put(KEY_HOUR, hour);
        args.put(KEY_MINUTE, minute);
        args.put(KEY_AMPM, ampm);
        args.put(KEY_EMAIL, email);
        args.put(KEY_DIFFERENTSITE, differentSite);
        args.put(KEY_SQUIRRELCHANGE, squirrelchange);
        args.put(KEY_COMMENTS, comments);
        return db.update(DATABASE_TABLE, args, 
        		KEY_PROFILENAME+"=?",new String[]{profileName})>0;
    }
}
